var mysql = require('mysql');
var mssql = require('mssql')
var fs = require('fs')

class TDBMs {
    constructor() {
        this.apps = {
            rtcChannel: {
                dbInfo: {
                    server: 'video.xmgkfw.cn',
                    user: 'sa',
                    password: 'ABCabc123',
                    //database    : 'rtcChannel',
                    database: 'rtcChannel',
                    port: 8201,
                    options: {
                        encrypt: true //使用windows azure，需要设置次配置。
                    },
                    pool: {
                        min: 0,
                        idleTimeoutMillis: 3000
                    }
                },
                sqlPath: "D:/gitee/hhudev/svr/datas/sqls"
            },
            directing: {
                dbInfo: {
                    server: 'video.xmgkfw.cn',
                    user: 'sa',
                    password: 'ABCabc123',
                    //database    : 'rtcChannel',
                    database: 'rtcChannel',
                    port: 8201,
                    options: {
                        encrypt: true //使用windows azure，需要设置次配置。
                    },
                    pool: {
                        min: 0,
                        idleTimeoutMillis: 3000
                    }
                },
                sqlPath: "D:/gitee/hhudev/svr/datas/sqls"
            }
        };
        this.pool = {};
    };
    query(appId, sql, ps, callback) {
        var me = this;
        var queryRes = {
            appId: appId,
            result: null
        };
        try {
            //appId = 'directing';
            if (!me.apps[appId]) {
                queryRes.error = "appId is error.";
                callback(queryRes);
                return;
            }

            if (me.pool[appId] == null) {
                me.pool[appId] = new mssql.ConnectionPool(me.apps[appId].dbInfo);
            }
            me.pool[appId].connect(function(errConn, connection) {
                if (errConn) {
                    queryRes.error = errConn;
                    callback(queryRes);
                } else {
                    var dbReq = connection.request();
                    if (ps && ps != null) {
                        var getType = function(arg) {
                            var dataType = mssql.NVarChar;
                            if (arg && typeof(arg) == "string") {
                                dataType = mssql.NVarChar;
                            } else if (arg && typeof(arg) == "number" && !isNaN(arg)) {
                                if (Number.isInteger(arg)) {
                                    dataType = mssql.Int;
                                } else {
                                    dataType = mssql.Float;
                                }
                            }
                            return dataType;
                        }
                        for (var p in ps) {
                            var v = ps[p];
                            var vType = getType(v);
                            dbReq.input(p, vType, v);
                        }
                    }
                    dbReq.query(sql, function(erQuery, resQuery) {
                        try {
                            queryRes.result = resQuery;
                            callback(queryRes);
                        } catch (eer) {
                            console.log(eer);
                        }
                    })
                    connection.release();
                }
            })
        } catch (e) {
            console.log(e)
        }
    };
    queryByFn(appId, sqlPath, ps, callback) {
        var me = this;
        var queryRes = {
            result: null
        };
        try {
            if (!me.apps[appId]) {
                queryRes.error = "appId is error.";
                callback(queryRes);
                return;
            }

            var fn = [me.apps[appId].sqlPath, sqlPath].join('/');
            fn = fn.replace("\\", '/').replace('//', '/');
            fn = fn + (fn.indexOf('.txt') > 0 ? "" : ".txt");
            if (fs.existsSync(fn)) {
                var sql = fs.readFileSync(fn, 'utf-8');
                me.query(appId, sql, ps, callback);
            } else {
                queryRes.error = "query Key is error.[" + sqlFn + "]";
                callback(queryRes);
            }
        } catch (e) {
            console.log(e)
        }
    };
    setConfig(dbKey, dbInfo) {
        var me = this;
        try {
            me.apps[dbKey] = dbInfo;
        } catch (er) {
            console.log(er);
        }
    };
    regist2WebSvr(webSvr) {
        var me = this;
        if (webSvr && webSvr != null) {
            var respJson = function(result, response) {
                try {
                    response.send(JSON.stringify(result));
                } catch (eer) {
                    console.log(eer);
                }
            }
            webSvr.registRoute("common", "msdb", "query", function(result, request, response) {
                try {
                    var appId = result.ps.appId;
                    var key = result.ps.key;
                    var psJson = result.ps.psJson;
                    var ps = {};
                    try {
                        ps = JSON.parse(psJson);
                    } catch (eer) {
                        ps = {};
                    }
                    me.queryByFn(appId, key, ps, function(res) {
                        result.data = res;
                        respJson(result, response);
                    });
                } catch (err) {
                    console.log(err);
                    respJson(result, response);
                }
            });
        }
    }
}
class TDBMySql {
    constructor() {
        this.apps = {
            kms: {
                host: "tjiot.hhdata.cn",
                port: 53306,
                uid: "root",
                pwd: "hhuc1115",
                db: "kmsv10",
                sqlPath: "E:/app/kmsdev/dev/svr/sqls"
            },
            hhRemoteAssist: {
                host: "tjiot.hhdata.cn",
                port: 53306,
                uid: "root",
                pwd: "hhuc1115",
                db: "hhRemoteAssist",
                sqlPath: "E:/app/kmsdev/dev/svr/sqls"
            }
        };
        this.pools = {};
    }
    getConn(appName, callback) {
        var me = this;
        var res = { conn: null };
        try {
            if (me.apps[appName]) {
                var dbInfo = me.apps[appName];
                if (!dbInfo.pool) {
                    dbInfo.pool = mysql.createPool({
                        host: dbInfo.host,
                        port: dbInfo.port,
                        user: dbInfo.uid,
                        password: dbInfo.pwd,
                        database: dbInfo.db,
                        connectionLimit: 100,
                        multipleStatements: true
                    });
                }
                dbInfo.pool.getConnection(function(err, conn) {
                    if (conn && conn != null) {
                        conn.config.queryFormat = function(query, values) {
                            if (!values) return query;
                            return query.replace(/\:(\w+)/g, function(txt, key) {
                                if (values.hasOwnProperty(key)) {
                                    return this.escape(values[key]);
                                }
                                return txt;
                            }.bind(this));
                        };

                    }
                    res.error = err;
                    res.conn = conn;
                    callback(res);
                })
            } else {
                res.error = "appName is error."
                callback(res);
            }
        } catch (e) {
            console.log(e);
        }
    };
    query(appId, sql, ps, callback) {
        var res = { result: null };
        var me = this;
        try {
            me.getConn(appId, function(resConn) {
                if (resConn.error) {
                    callback(resConn);
                } else {
                    var s = resConn.conn.format(sql, ps);
                    console.log(s);
                    resConn.conn.query(sql, ps, function(errQuery, results, fields) {
                        resConn.conn.release();
                        if (errQuery) {
                            var s = resConn.conn.format(sql, ps);
                            res.error = errQuery;
                            console.log(errQuery, s, ps);
                            callback(res);
                        } else {
                            res.result = JSON.parse(JSON.stringify(results));
                            callback(res);
                        }
                    })
                }
            })
        } catch (e) {
            console.log(e);
        }
    };
    queryByFn(appName, sqlPath, ps, callback) {
        var res = { result: null };
        var me = this;
        try {
            if (me.apps[appName]) {
                var absPath = me.apps[appName].sqlPath + "/" + sqlPath;
                absPath = absPath.replace("//", "/");
                if (absPath.indexOf(".txt") <= 0) {
                    absPath = absPath + ".txt";
                }
                fs.exists(absPath, function(flag) {
                    if (flag) {
                        var sql = fs.readFileSync(absPath, 'utf-8');
                        me.query(appName, sql, ps, function(resDB) {
                            res = resDB;
                            callback(res);
                        })
                    } else {
                        res.error = "sqlPath is error.";
                        callback(res);
                    }
                })
            } else {
                res.error = "appName is error."
                callback(res);
            }
        } catch (e) {
            console.log(e);
        }
    };
    setConfig(dbKey, dbInfo) {
        var me = this;
        try {
            me.apps[dbKey] = dbInfo;
        } catch (er) {
            console.log(er);
        }
    };
    regist2WebSvr(webSvr) {
        var me = this;
        if (webSvr && webSvr != null && webSvr.registRoute) {
            var respJson = function(result, response) {
                try {
                    response.send(JSON.stringify(result));
                } catch (eer) {
                    console.log(eer);
                }
            }
            webSvr.registRoute("common", "mysqldb", "query", function(result, request, response) {
                try {
                    var appId = result.ps.appId;
                    var key = result.ps.key;
                    var psJson = result.ps.psJson;
                    var ps = {};
                    try {
                        ps = JSON.parse(psJson);
                    } catch (eer) {
                        ps = {};
                    }
                    me.queryByFn(appId, key, ps, function(res) {
                        result.data = res;
                        respJson(result, response);
                    });
                } catch (err) {
                    console.log(err);
                    respJson(result, response);
                }
            });
        }
    }
}

module.exports = {
    TDBMs: TDBMs,
    TDBMySql: TDBMySql
}